--USE MovieMgr;

IF EXISTS (SELECT 1 FROM sysobjects where name='SeriesDetailsView' and type='v')
DROP VIEW SeriesDetailsView
GO

--SELECT * FROM SeriesDetailsView
CREATE VIEW SeriesDetailsView
AS
SELECT EpisodeMain.PKey  
	,EpisodeMain.StagePKey  
	,EpisodeMain.SerialNo  
	,EpisodeMain.tmpLoaned  
	,CASE WHEN EpisodeMain.PKey <> EpisodeMain.SeriesParentPKey then
	 's'+CASE WHEN EpisodeMain.nSeason<10 then '0' else '' end + CONVERT(varchar,EpisodeMain.nSeason)  
	  +CASE WHEN EpisodeMain.nEpisode<10 THEN '0' ELSE '' END+
	 'e'+CASE WHEN EpisodeMain.nEpisode<10 THEN '0' ELSE '' END + CONVERT(varchar,EpisodeMain.nEpisode)  
	  +' - ' ELSE ''
	 END + EpisodeMain.Title as Title  
	,EpisodeMain.AlternateTitle  
	,EpisodeMain.IMDbCode as ID  
	,EpisodeMain.SeriesParentPKey  
	,SeriesMain.Title as SeriesTitle  
	,SeriesMain.IMDbCode as SeriesID  
	,case(EpisodeMain.tmpSeen) when 1 then 'Y' else 'N' end as "tmpSeen"  
	,2*COALESCE(Watchlist.DateAdded,0)-COALESCE(Watchlist.DateWatched,0) as "Seen"  
	,EpisodeMain.IMDbRating as "Rating"  
	,EpisodeMain.CustomRating as "MyRating"  
	,EpisodeMain.tmpDirector as "tmpDirector"  
	,COALESCE(DirectorMain.Name,'') as "Director"  
	,COALESCE(VolumeMain.Volume,'') AS Volume  
	,EpisodeMain.tmpGenre as "Genre"  
	,EpisodeMain.ReleaseYear as "Year"  
	,EpisodeMain.Studio  
	,COALESCE(CollectionMain.Collection,'') AS Collection   
	-- ,EpisodeMain.Casting
	,CastingMain.Casting  
	,EpisodeMain.Plot  
	,EpisodeMain.Notes  
	,EpisodeMain.tmpCountry as "Country"  
	,EpisodeMain.Poster as "Poster"  
	,EpisodeMain.PosterLocation as "PosterLocation"  
	,EpisodeMain.Classification  
	,EpisodeMain.nDiscs  
	,EpisodeMain.RunTimeMins as "RunTime"  
	,COALESCE(LayerMain.Layer,'') AS Layer  
	,COALESCE(ColourMain.Colour,'') AS Colour  
	,COALESCE(RegionMain.Region,'') AS Region  
	,COALESCE(MediumMain.Medium,'') AS Medium  
	,COALESCE(VCodecMain.VCodec,'') AS VCodec  
	,'' AS ACodec --,COALESCE(ACodecMain.ACodec,'--') AS ACodec  
	,COALESCE(CondMain.Condition,'') AS Condition  
	,COALESCE(EpisodeMain.IMDbLink,'') AS IMDbLink  
	,COALESCE(EpisodeMain.IMDbTrailerLink,'') AS IMDbTrailerLink  
	,COALESCE(EpisodeMain.OfficialSite,'') AS OfficialSite  
	,COALESCE(EpisodeMain.WikipediaLink,'') AS WikipediaLink  
	,EpisodeMain.MyReviews  
	,EpisodeMain.Status  

	,EpisodeMain.VolumeMainPKey  
	,EpisodeMain.CollectionMainPKey  
	,EpisodeMain.LayerMainPKey  
	,EpisodeMain.ColourMainPKey  
	,EpisodeMain.RegionMainPKey  
	,EpisodeMain.MediumMainPKey  
	,EpisodeMain.VCodecMainPKey  
	,0 as ACodecMainPKey --EpisodeMain.ACodecMainPKey  
	,EpisodeMain.CondMainPKey  
	,RelDirector.PKey as RelDirectorPKey  
  
FROM EntertainmentMain EpisodeMain  
INNER JOIN EntertainmentMain SeriesMain ON EpisodeMain.SeriesParentPKey=SeriesMain.PKey and SeriesMain.PKey=SeriesMain.SeriesParentPKey and EpisodeMain.Status<>'d' and SeriesMain.Status<>'d'  
LEFT OUTER JOIN MuveeDirectorRel RelDirector LEFT OUTER JOIN PeopleMain DirectorMain ON DirectorMain.PKey=RelDirector.PeopleMainPKey AND DirectorMain.Status<>'d' AND RelDirector.Status<>'d' ON EpisodeMain.PKey=RelDirector.MuveeMainPKey AND RelDirector.Status<>'d'
LEFT OUTER JOIN VolumeMain ON VolumeMain.PKey=EpisodeMain.VolumeMainPKey and VolumeMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN CollectionMain ON CollectionMain.PKey=EpisodeMain.CollectionMainPKey and CollectionMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN LayerMain ON LayerMain.PKey=EpisodeMain.LayerMainPKey and LayerMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN ColourMain ON ColourMain.PKey=EpisodeMain.ColourMainPKey and ColourMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN RegionMain ON RegionMain.PKey=EpisodeMain.RegionMainPKey and RegionMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN MediumMain ON MediumMain.PKey=EpisodeMain.MediumMainPKey and MediumMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN VCodecMain ON MediumMain.PKey=EpisodeMain.VCodecMainPKey and VCodecMain.Status<>'d' and EpisodeMain.Status<>'d'  
--LEFT OUTER JOIN ACodecMain ON MediumMain.PKey=EpisodeMain.ACodecMainPKey and ACodecMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN CondMain ON MediumMain.PKey=EpisodeMain.CondMainPKey and CondMain.Status<>'d' and EpisodeMain.Status<>'d'  
LEFT OUTER JOIN CastingMain ON CastingMain.EntertainmentMainPKey=EpisodeMain.PKey and CastingMain.Status<>'d' and EpisodeMain.Status<>'d'
LEFT OUTER JOIN  
(
	SELECT w1.EntertainmentMainPKey,  
		CASE WHEN DateAdded IS NOT NULL THEN 1 ELSE 0 END as DateAdded,  
		CASE WHEN DateWatched IS NOT NULL THEN 1 ELSE 0 END as DateWatched  
	FROM WatchlistMain w1  
	WHERE Status<>'d'  
	and w1.PKey IN  
	(
		SELECT TOP 1 PKey  
		FROM WatchlistMain w2  
		WHERE Status<>'d'  
		and w1.EntertainmentMainPKey=w2.EntertainmentMainPKey  
		ORDER BY PKey DESC  
	)
) Watchlist ON Watchlist.EntertainmentMainPKey=EpisodeMain.PKey and EpisodeMain.Status<>'d'  
  
WHERE EpisodeMain.Status<>'d'  
 and EpisodeMain.SeriesParentPKey<>''  


----SELECT SeriesMain.PKey
----	,SeriesMain.StagePKey
----	,SeriesMain.SerialNo
----	,SeriesMain.tmpLoaned
----	,SeriesMain.Title
----	,SeriesMain.AlternateTitle
----	,SeriesMain.IMDbCode as "ID"
----	,case(SeriesMain.tmpSeen) when 1 then 'Y' else 'N' end as "tmpSeen"
----	,2*COALESCE(Watchlist.DateAdded,0)-COALESCE(Watchlist.DateWatched,0) as "Seen"
----	,SeriesMain.IMDbRating as "Rating"
----	,SeriesMain.CustomRating as "MyRating"
----	,SeriesMain.tmpDirector as "tmpDirector"
----	,COALESCE(DirectorMain.Name,'') as "Director"
----	,COALESCE(VolumeMain.Volume,'') AS Volume
----	,SeriesMain.tmpGenre as "Genre"
----	,SeriesMain.ReleaseYear as "Year"
----	,SeriesMain.Studio
----	,COALESCE(CollectionMain.Collection,'') AS Collection	
----	,SeriesMain.Cast
----	,SeriesMain.Plot
----	,SeriesMain.Notes
----	,SeriesMain.tmpCountry as "Country"
----	,SeriesMain.Poster as "Poster"
----	,SeriesMain.PosterLocation as "PosterLocation"
----	,SeriesMain.Classification
----	,SeriesMain.nDiscs
----	,SeriesMain.RunTimeMins as "Run_Time"
----	,COALESCE(LayerMain.Layer,'') AS Layer
----	,COALESCE(ColourMain.Colour,'') AS Colour
----	,COALESCE(RegionMain.Region,'') AS Region
----	,COALESCE(MediumMain.Medium,'') AS Medium
----	,COALESCE(VCodecMain.VCodec,'') AS VCodec
----	,'' AS ACodec --,COALESCE(ACodecMain.ACodec,'--') AS ACodec
----	,COALESCE(CondMain.Condition,'') AS Condition
----	,COALESCE(SeriesMain.IMDbLink,'') AS IMDbLink
----	,COALESCE(SeriesMain.IMDbTrailerLink,'') AS IMDbTrailerLink
----	,COALESCE(SeriesMain.OfficialSite,'') AS OfficialSite
----	,COALESCE(SeriesMain.WikipediaLink,'') AS WikipediaLink
----	,SeriesMain.MyReviews
----	,SeriesMain.Status
----	
----	,SeriesMain.VolumeMainPKey
----	,SeriesMain.CollectionMainPKey
----	,SeriesMain.LayerMainPKey
----	,SeriesMain.ColourMainPKey
----	,SeriesMain.RegionMainPKey
----	,SeriesMain.MediumMainPKey
----	,SeriesMain.VCodecMainPKey
----	,0 as ACodecMainPKey --SeriesMain.ACodecMainPKey
----	,SeriesMain.CondMainPKey
----	,RelDirector.PKey as RelDirectorPKey
----
----FROM EntertainmentMain SeriesMain
----LEFT OUTER JOIN MuveeDirectorRel RelDirector LEFT OUTER JOIN PeopleMain DirectorMain ON DirectorMain.PKey=RelDirector.PeopleMainPKey AND DirectorMain.Status<>'d' AND RelDirector.Status<>'d' ON SeriesMain.PKey=RelDirector.MuveeMainPKey AND RelDirector.Status<>'d'
----LEFT OUTER JOIN VolumeMain ON VolumeMain.PKey=SeriesMain.VolumeMainPKey and VolumeMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN CollectionMain ON CollectionMain.PKey=SeriesMain.CollectionMainPKey and CollectionMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN LayerMain ON LayerMain.PKey=SeriesMain.LayerMainPKey and LayerMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN ColourMain ON ColourMain.PKey=SeriesMain.ColourMainPKey and ColourMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN RegionMain ON RegionMain.PKey=SeriesMain.RegionMainPKey and RegionMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN MediumMain ON MediumMain.PKey=SeriesMain.MediumMainPKey and MediumMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN VCodecMain ON MediumMain.PKey=SeriesMain.VCodecMainPKey and VCodecMain.Status<>'d' and SeriesMain.Status<>'d'
------LEFT OUTER JOIN ACodecMain ON MediumMain.PKey=SeriesMain.ACodecMainPKey and ACodecMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN CondMain ON MediumMain.PKey=SeriesMain.CondMainPKey and CondMain.Status<>'d' and SeriesMain.Status<>'d'
----LEFT OUTER JOIN
----(
----	SELECT w1.EntertainmentMainPKey,
----		CASE WHEN DateAdded IS NOT NULL THEN 1 ELSE 0 END as DateAdded,
----		CASE WHEN DateWatched IS NOT NULL THEN 1 ELSE 0 END as DateWatched
----	FROM WatchlistMain w1
----	WHERE Status<>'d'
----	and w1.PKey IN
----	(
----		SELECT TOP 1 PKey
----		FROM WatchlistMain w2
----		WHERE Status<>'d'
----		and w1.EntertainmentMainPKey=w2.EntertainmentMainPKey
----		ORDER BY PKey DESC
----	)
----) Watchlist ON Watchlist.EntertainmentMainPKey=SeriesMain.PKey and SeriesMain.Status<>'d'
----
----WHERE SeriesMain.Status<>'d'
----	and SeriesMain.SeriesParentPKey<>''
